/** --------------------------------------------------------------------- 
 Copyright(C) 	2012-2012 北京可为时代   	
 All rights reserved
 
 项目名		  : kwcrm
 文件名		  : ImportExcel.java
 文件建立者	      : 孙立伟
 当前文件类型	  : 
 文件基本功能	  : 
 文件建立日期	  : Mar 18, 2013
 
 修改历史:
   日期 			    修改人 			    版本 			    描述
--------------	--------------	--------------	--------------
 Mar 18, 2013		孙立伟      		    1.0				    1.0 Version
-------------------------------------------------------------------*/

package com.siqi.hr.common.exportexcel;

import com.siqi.hr.common.exportexcel.impl.ExcelProcessor;
import com.siqi.hr.common.exportexcel.impl.XRow;
import com.siqi.hr.common.exportexcel.impl.XSheet;
import com.siqi.hr.common.utils.Const;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导入Excel文件
 * 
 * @version 1.0.0, Mar 18, 2013
 * @since JDK1.6.0_12, Spring3.0.2
 */
public class ImportExcel extends ExcelProcessor {
	private Map<String, Object>  globalmap = new HashMap<String, Object>();//全局
	private Map<Integer, String> sheetMap = new HashMap<Integer, String>();//工作薄
	private List<Map<Integer, Object>> list = new ArrayList<Map<Integer, Object>>();//数据
	private List<String> cells = new ArrayList<String>();//列
	private Map<Integer, List<Map<Integer, Object>>> listMap  = new HashMap<Integer, List<Map<Integer, Object>>>();//多sheet数据
	private Map<Integer, List<String>> cellsMap = new HashMap<Integer, List<String>>();//多sheet列
	int sheetTag = 0;
	
	/**
	 * 日志
	 */
	public static Logger logger = Logger.getLogger(ImportExcel.class);
	
	public ImportExcel(String fileName) throws Exception {
		super(fileName);
	}
	
	@Override
	public void processSheet(XSheet xSheet) {
		sheetMap.put(xSheet.getNumber(), xSheet.getName());
	}
	
	@Override
	public void processRow(XRow row) {
		boolean boo = false;
		//每当getRowIndex = 1 时意味着一个新的sheet开始了
		if(row.getRowIndex() == 1){
			boo = true;
			sheetTag = sheetTag+1;
		}else {
			boo = false;
		}
		Map<Integer, Object> map = new HashMap<Integer, Object>();
		for (int i = 0; i < row.getCellsSize(); i++) {
			//行索引为0时表示列头
			if(row.getRowIndex() == 1){
				cells.add(row.getCell(i).getValue());
			}else {
				map.put( row.getCell(i).getColumnIndex(), row.getCell(i).getValue());
			}
		}
		if(cells.size()>0){
			List<String> tempCells = new ArrayList<String>();
			tempCells.addAll(cells);
			cellsMap.put(sheetTag,tempCells );
			cells.clear();
		}
		if(!map.isEmpty()){//只有当MAP为非空时
			list.add(map);
		}
		//将list和cells封装到一个sheet中
		if(boo && sheetTag != 1){
			List<Map<Integer, Object>> tempList = new ArrayList<Map<Integer, Object>>();
			tempList.addAll(list);
			listMap.put(sheetTag-1, tempList);
			list.clear();
		}
	}
	
    public Map<String, Object> ReadExcelByHandler() throws Exception{
    	processByRow();
    	listMap.put(sheetTag, list);
    	globalmap.put("cellsMap", cellsMap);
		globalmap.put("listMap", listMap);
		globalmap.put("sheetMap", sheetMap);
    	return globalmap;
	}
	
	
	/**
	 * 对于大数据读取 
	 * @throws Exception 
	 * */
	public Map<String, Object> getExcelAllDataList(String path, int kind) throws Exception{
		Map<String, Object>  map = new HashMap<String, Object>();
		map = ReadExcelByHandler();
		return map;
	}
	
	/**
	 *得到Excel列头信息
	 *@author 孙立伟 Apr 13, 2013
	 *@param path  文件路径
	 *@param kind  文件类型
	 *@return
	 *@throws IOException 
	 *List<String>
	 */
	public List<String> ReadExcelCell(String path, int kind) throws IOException{
		List<String> list = new ArrayList<String>();
		if(kind == 2){
			logger.info("读取2007列头");
			list = ReadExcelPresentVersionCell(path);
		}else if (kind == 1) {
			logger.info("读取2003列头");
			list = ReadExcelOldVersionCell(path);
		}else {
			//抛出一个异常
			logger.info("导入Excel类型异常");
		}
		return list;
	}
	
	/**
	 * 得到导入文件的列头信息Excel(2003)
	 *@author 孙立伟 Mar 26, 2013
	 *@param path  文件路径
	 *@return
	 *@throws IOException 
	 *List<String>
	 */
	public List<String> ReadExcelOldVersionCell(String path) throws IOException{
		List<String> cells = new ArrayList<String>();
		
		FileInputStream finput = new FileInputStream(path);
		POIFSFileSystem fs = new POIFSFileSystem(finput);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = null;
		HSSFCell cell = null;
		for (int i = 0; i < sheet.getLastRowNum(); i++) {
			row = sheet.getRow(i);
			if(i == 0){
				// 判断储存格的格式
				for (int y = 0; y < row.getLastCellNum(); y++) {
	        		cell = row.getCell(y);
	        		switch (cell.getCellType()) {
	        		case HSSFCell.CELL_TYPE_STRING:
	        			cells.add(cell.getStringCellValue());//列头都为String类型
	        			break;
	        		default:
	        			logger.info("不明的格式");
	        		break;
	        		}
				}
			}else {
				break;//只执行读取列头
			}
		}
		return cells;
	}
	
    /**
     *根据文件路径读取Excel(2003)数据(无数据校验)
     *@author 孙立伟 Mar 18, 2013
     *@param path  Excel文件所在路径
     *@return List  返回list类型的Excel数据 
     *@throws IOException 
     */
    public Map<String, Object> ReadExcelOldVersion(String path) throws IOException{
    	Map<String, Object>  globalmap = new HashMap<String, Object>();
    	List<Map<Integer, Object>> list = new ArrayList<Map<Integer, Object>>();//数据
    	List<String> cells = new ArrayList<String>();
         
    	FileInputStream finput = new FileInputStream(path);
		POIFSFileSystem fs = new POIFSFileSystem(finput);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = null;
		HSSFCell cell = null;
		
//		logger.info("getPhysicalNumberOfCells列数: "+sheet.getRow(0).getPhysicalNumberOfCells());//得到全部不为空的行
//    	logger.info("getPhysicalNumberOfRows行数: "+sheet.getPhysicalNumberOfRows());//得到全部不为空的列(这可不行)
//    	logger.info("getLastCellNum列数: "+sheet.getRow(0).getLastCellNum());//得到最后一个不为空的行
//    	logger.info("getLastRowNum行数: "+sheet.getLastRowNum());//得到最后一个不为空的列
		
    	boolean boo = false;
		for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if(i == 0){
				// 判断储存格的格式
				for (int y = 0; y < row.getLastCellNum(); y++) {
	        		cell = row.getCell(y);
	        		switch (cell.getCellType()) {
	        		case HSSFCell.CELL_TYPE_STRING:
	        			if(cell.getStringCellValue().equals(Const.IMPORT_ERROR_INFO)){
	        				boo = true;
	        			}else {
	        				//如果导入的文件的中包含"导入错误信息"列头，则直接PASS
	        				logger.info("String ** "+cell.getStringCellValue());
	        				cells.add(cell.getStringCellValue());//列头都为String类型
						}
	        			break;
	        		default:
	        			logger.info("不明的格式");
	        		break;
	        		}
				}
			}else {
				int cellNum = row.getLastCellNum();//得到最后一个不为空的列
				if(boo){
					cellNum = cellNum-1;
				}
	        	Map<Integer, Object> map=new HashMap<Integer, Object>();
	        	logger.info("当前行数： "+i);
	        	for (int y = 0; y < cellNum; y++) {
	        		cell = row.getCell(y);
						// 判断储存格的格式
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_NUMERIC:
						if(HSSFDateUtil.isCellDateFormatted(cell)){
							double d = cell.getNumericCellValue();      
					        Date date = HSSFDateUtil.getJavaDate(d);
					        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							String dstr = sdf.format(date);
					        map.put(y, dstr);
						}else {
							map.put(y, cell.getNumericCellValue());
						}
						break;
					case HSSFCell.CELL_TYPE_STRING:
						map.put(y, cell.getStringCellValue());
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						map.put(y, cell.getNumericCellValue());
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						map.put(y, "空");
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
				    	map.put(y, cell.getBooleanCellValue());
				    	break;
				    case HSSFCell.CELL_TYPE_ERROR:
				    	map.put(y, cell.getErrorCellValue());//竟然返回byte
				    	break;
					default:
						logger.info("不明的格式");
						break;
					}
				}
	        	list.add(map);
			}
		}
		globalmap.put("cells", cells);
		globalmap.put("list", list);
    	return globalmap;
    }
    
    /**
     *得到Excel(2007)文件列头
     *@author 孙立伟 Apr 13, 2013
     *@param path  文件路径
     *@return
     *@throws IOException 
     *List<String>
     */
    public List<String> ReadExcelPresentVersionCell(String path) throws IOException{
		List<String> cells = new ArrayList<String>();
		File file = new File(path);
    	FileInputStream fps = null;
    	if(file.exists()){
    		fps = new FileInputStream(file);
    	}
    	XSSFWorkbook xwk = new XSSFWorkbook(fps);
    	XSSFSheet sheet = xwk.getSheetAt(0);
    	XSSFRow row = null;
    	XSSFCell cell = null;
		for (int i = 0; i < sheet.getLastRowNum(); i++) {
			row = sheet.getRow(i);
			if(i == 0){
				// 判断储存格的格式
				for (int y = 0; y < row.getLastCellNum(); y++) {
	        		cell = row.getCell(y);
	        		switch (cell.getCellType()) {
	        		case HSSFCell.CELL_TYPE_STRING:
	        			//logger.info("String ** "+cell.getStringCellValue());
	        			cells.add(cell.getStringCellValue());//列头都为String类型
	        			break;
	        		default:
	        			logger.info("不明的格式");
	        		break;
	        		}
				}
			}else {
				break;//只执行读取列头
			}
		}
		return cells;
	}
    
    /**
     *根据文件路径读取Excel(2007)数据(无数据校验)
     *@author 孙立伟 Mar 18, 2013
     *@param path  Excel文件所在路径
     *@return List  返回list类型的Excel数据 
     *@throws IOException 
     */
    public Map<String, Object> ReadExcelPresentVersion(String path) throws IOException{
    	Map<String, Object>  globalmap = new HashMap<String, Object>();
    	List<Map<Integer, Object>> list = new ArrayList<Map<Integer, Object>>();//数据
    	List<String> cells = new ArrayList<String>();
 
    	File file = new File(path);
    	FileInputStream fps = null;
    	if(file.exists()){
    		fps = new FileInputStream(file);
    	}
    	XSSFWorkbook xwk = new XSSFWorkbook(fps);
    	XSSFSheet sheet = xwk.getSheetAt(0);
    	XSSFRow row = null;
    	XSSFCell cell = null;
    	//读取Excel数据分为两部分：第一为列头，第二为数据本身
//    	logger.info("列数: "+sheet.getRow(0).getPhysicalNumberOfCells());
//    	logger.info("行数: "+sheet.getPhysicalNumberOfRows());

    	boolean boo = false;
    	for(int i=0; i<sheet.getPhysicalNumberOfRows(); i++){
    		row = sheet.getRow(i);
    		if(i == 0){
    			// 判断储存格的格式
				for (int y = 0; y < row.getLastCellNum(); y++) {
	        		cell = row.getCell(y);
	        		switch (cell.getCellType()) {
	        		case HSSFCell.CELL_TYPE_STRING:
	        			if(cell.getStringCellValue().equals(Const.IMPORT_ERROR_INFO)){
	        				boo = true;
	        			}else {
	        				logger.info("String ** "+cell.getStringCellValue());
	        				cells.add(cell.getStringCellValue());//列头都为String类型
						}
	        			break;
	        		default:
	        			logger.info("不明的格式");
	        		break;
	        		}
				}
    		}else {
    			int cellNum = row.getLastCellNum();//得到最后一个不为空的列
				if(boo){
					cellNum = cellNum-1;
				}
    			Map<Integer, Object> map = new HashMap<Integer, Object>();
    			for(int y=0; y<row.getLastCellNum(); y++){
    				cell = row.getCell(y);
    				switch (cell.getCellType()) {
    				case XSSFCell.CELL_TYPE_STRING:
    					logger.info("String ** "+cell.getStringCellValue());
    					map.put(y, cell.getStringCellValue());
    					break;
    				case XSSFCell.CELL_TYPE_NUMERIC:
    					logger.info("Number ** "+cell.getNumericCellValue());
    					map.put(y, cell.getNumericCellValue());
    					break;
    				case XSSFCell.CELL_TYPE_FORMULA:
    					//当一列的结果为公式计算出来的，那么应该根据其结果的类型使用响应的方法获得公式计算出来的结果
    					logger.info("Formula ** "+cell.getNumericCellValue());
    					map.put(y, cell.getNumericCellValue());
    					break;
    				case XSSFCell.CELL_TYPE_BLANK:
    					logger.info("Blank ** 空");
    					map.put(y, "空");
    					break;
    				case XSSFCell.CELL_TYPE_BOOLEAN:
    					logger.info("Boolean ** "+cell.getBooleanCellValue());
    					map.put(y, cell.getBooleanCellValue());
    					break;
    				case XSSFCell.CELL_TYPE_ERROR:
    					logger.info("Error ** "+cell.getErrorCellString());
    					map.put(y, cell.getErrorCellString());
    					break;
    				default:
    					//应该抛出异常
    					logger.info("未定义");
    				break;
    				}
    			}
    			list.add(map);
			}
    	}
    	globalmap.put("cells", cells);
		globalmap.put("list", list);
    	return globalmap;
    }

}
